<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 3600);

define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
	include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
	define('JPATH_BASE', dirname(__FILE__));
	require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();
         
require_once JPATH_BASE.DS.'PHPExcel'.DS.'PHPExcel'.DS.'IOFactory.php';
if (!file_exists(JPATH_BASE.DS."masterlist.xlsx")) {
    exit("File does not exist.\n");
}

$objPHPExcel = PHPExcel_IOFactory::load(JPATH_BASE.DS."masterlist.xlsx");
$activeSheet = $objPHPExcel->getActiveSheet();

//echo ->getCell('A1')->getValue().'<br />';
$size = $objPHPExcel->getActiveSheet()->getHighestRow();

$db = JFactory::getDbo();
$query = 'SELECT member_no FROM #__masterlist';
$db->setQuery($query);
$listMaster = $db->loadResultArray();

//echo $size; die;
//start:
$count=0;
for($i=2; $i<=$size; $i++)
{

	if(trim($activeSheet->getCell('A'.$i)->getValue()) == ''){
		break;
	}
    $obj = new stdClass();
	$obj->member_no = $activeSheet->getCell('A'.$i)->getValue();
	$obj->title = $activeSheet->getCell('B'.$i)->getValue();
    $obj->member_mailing = $activeSheet->getCell('C'.$i)->getValue();
    
    /********* Date *********/
    if($activeSheet->getCell('D'.$i)->getValue() != '')
    {
        date_default_timezone_set('Asia/Ho_Chi_Minh'); 
		$obj->date_of_birth = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($activeSheet->getCell('D'.$i)->getValue()));    
		$obj->date_of_birth .= " 23:59:59";
		$obj->date = date('d', strtotime($obj->date_of_birth));
        $obj->month = date('m', strtotime($obj->date_of_birth));
        $obj->year = date('Y', strtotime($obj->date_of_birth));
    }
    else
    {
        $obj->date = '';
        $obj->month = '';
        $obj->year = '';
    }
    
    $obj->mobile_no = $activeSheet->getCell('E'.$i)->getValue();
    $obj->home_no = $activeSheet->getCell('F'.$i)->getValue();
    $obj->email = $activeSheet->getCell('G'.$i)->getValue();
    
    $obj->member_residential_address_line_1 = $activeSheet->getCell('H'.$i)->getValue();
    $obj->member_residential_address_line_2 = $activeSheet->getCell('I'.$i)->getValue();
    $obj->member_residential_address_line_3 = $activeSheet->getCell('J'.$i)->getValue();
    $obj->member_residential_city = $activeSheet->getCell('K'.$i)->getValue();
    $obj->organisation_name = $activeSheet->getCell('L'.$i)->getValue();
    
    $obj->occupation = $activeSheet->getCell('M'.$i)->getValue();
    $obj->organisation_postal_address_1 = $activeSheet->getCell('N'.$i)->getValue();
    $obj->organisation_postal_address_2 = $activeSheet->getCell('O'.$i)->getValue();
    $obj->organisation_postal_address_3 = $activeSheet->getCell('P'.$i)->getValue();
    $obj->demographic_group = $activeSheet->getCell('Q'.$i)->getValue();
    
    $obj->nationality = $activeSheet->getCell('R'.$i)->getValue();
    $obj->loyalty_points = $activeSheet->getCell('S'.$i)->getValue();
    //$obj->swipe_card = $activeSheet->getCell('T'.$i)->getValue();
    
    /********* Date *********/
    if($activeSheet->getCell('U'.$i)->getValue() != '')
    {
        $obj->member_joining_date = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($activeSheet->getCell('U'.$i)->getValue()));      
    }
    else
    {
        $obj->member_joining_date = '';   
    }
    
    
    /********* Date *********/
    if($activeSheet->getCell('V'.$i)->getValue() != '')
    {
        $obj->application_received_date = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($activeSheet->getCell('V'.$i)->getValue()));   
    }
    else
    {
        $obj->application_received_date = '';   
    }
    
    /********* Date *********/
    if($activeSheet->getCell('W'.$i)->getValue() != '')
    {
        $obj->delivered_vipcard_date = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($activeSheet->getCell('W'.$i)->getValue()));  
    }
    else
    {
        $obj->delivered_vipcard_date = '';   
    }
    
    
    /********* Date *********/
   
    $receiving_slip = $activeSheet->getCell('X'.$i)->getValue();
    if(is_string($receiving_slip)){
        $receiving_slip = "1900-01-01";
    }
    else if($receiving_slip == 0){
        $receiving_slip = NULL;
    }
    else {
        $receiving_slip = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($receiving_slip));
    }
        
    $obj->receiving_slip = $receiving_slip;
    
    $obj->application_received_from = $activeSheet->getCell('Y'.$i)->getValue();
    $obj->card_pick_up_arrangement 	= $activeSheet->getCell('Z'.$i)->getValue();
	$obj->bounce_email 			    = $activeSheet->getCell('AF'.$i)->getValue();	
    if($i == 2){
		$masterlist_created = $activeSheet->getCell('AG'.$i)->getValue();
	}
	$obj->masterlist_created = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($masterlist_created));
	$obj->status = 1;
	
    if(count($listMaster)){
		if(in_array($obj->member_no,$listMaster)){
			$obj->status = 0;
			$db->insertObject('#__masterlist', $obj);
			echo 'Member No: '.$obj->member_no.' exits (Need Updated newest info) <br />';
			$count++;
		}else{
			$db->insertObject('#__masterlist', $obj); 
			echo '<strong>Save member: '.$obj->member_no.'</strong><br />';
			$count++;
		}
	}
	else {	
		
		$db->insertObject('#__masterlist', $obj); 
		echo 'Save member: '.$obj->member_no.'<br />';
		$count++;
	}
}
echo "Save member successful.Insert total: <strong>".$count."</strong>record";